foreach x in "summary_stats" "score_distribution" {
	
	import excel "${raw_data}/NRMP/step1_`x'.xls", sheet("step1") firstrow clear
	
	cap rename ïspecialty specialty
	
	replace specialty = strtrim(specialty)
	ren specialty nrmp_spec_desc

	tempfile raw_`x'
	save "`raw_`x''"
}

replace score = "300" if score == ">260"

rename n_dist_* n_dist_*_

replace score = substr(score, -3, 3)

reshape wide n_dist*, i(nrmp_spec_desc year) j(score) string

merge m:1 nrmp_spec_desc year using "`raw_summary_stats'", assert(matched using) keep(matched) nogen

gen total_applicants_sn = n_match_sn + n_no_match_sn

label var n_dist_match_sn_180		"Number of U.S. Allopathic Seniors that matched with Score < 180"
label var n_dist_no_match_sn_180 	"Number of U.S. Allopathic Seniors that did not match with Score < 180"
label var n_dist_match_other_180 	"Number of Independent Applicants that matched with Score < 180"
label var n_dist_no_match_other_180	"Number of Independent Applicants that did not match with Score < 180"
	
forvalues x = 190(10)260 {
	label var n_dist_match_sn_`x' 		"Number of U.S. Allopathic Seniors that matched with Score: `=`x' - 10' <= X < `x'"
	label var n_dist_no_match_sn_`x' 	"Number of U.S. Allopathic Seniors that did not match with Score: `=`x' - 10' <= X < `x'"
	label var n_dist_match_other_`x' 	"Number of Independent Applicants that matched with Score: `=`x' - 10' <= X < `x'"
	label var n_dist_no_match_other_`x'	"Number of Independent Applicants that did not match with Score: `=`x' - 10' <= X < `x'"
}

label var n_dist_match_sn_300 		"Number of U.S. Allopathic Seniors that matched with Score > 260"
label var n_dist_no_match_sn_300	"Number of U.S. Allopathic Seniors that did not match with Score > 260"
label var n_dist_match_other_300 	"Number of Independent Applicants that matched with Score > 260"
label var n_dist_no_match_other_300	"Number of Independent Applicants that did not match with Score > 260"
label var avg_score_sn		"Mean Step 1 score for U.S. Senior applicants that matched (specialty-year)"
label var n_match_sn 		"Number of U.S. Allopathic Seniors that matched (specialty-year)"
label var n_match_other		"Number of Independent Applicants that matched (specialty-year)"
label var n_no_match_sn		"Number of U.S. Allopathic Seniors that did not match (specialty-year)"
label var n_no_match_other	"Number of Independent Applicants that did not match (specialty-year)"
label var n_no_resear_sn	"No. of U.S. Seniors that matched and reported no research experience (specialty-year)"
label var n_no_resear_other	"No. of Indepndent Applicants that matched and reported no research experience (specialty-year)"
label var n_no_pub_sn 		"No. of U.S. Seniors that matched and reported no abstracts, presentations, or publications (specialty-year)"
label var n_no_pub_other 	"No. of Independent Applicants that matched and reported no abstracts, presentations, or publications (specialty-year)"
label var share_aoa_sn 		"Share of Applicants that matched who are members of Alphad Omega Alpha (specialty-year)"
label var share_nih_sn_match	"Share of U.S. Seniors that matched and graduated from top 40 NIH schools (specialty-year)"
label var share_nih_sn_no_match "Share of U.S. Seniors that did not match and graduated from top 40 NIH schools (specialty-year)"

keep	year nrmp_spec_desc avg_score_sn ///
		n_match_sn n_no_match_sn n_match_other n_no_match_other	total_applicants_sn ///
		share_aoa_sn share_nih_sn_match share_nih_sn_no_match *resear* *pub* n_dist* 

order	year nrmp_spec_desc avg_score_sn ///
		n_match_sn n_no_match_sn n_match_other n_no_match_other	total_applicants_sn ///
		share_aoa_sn share_nih_sn_match share_nih_sn_no_match *resear* *pub* n_dist* 
		
isid year nrmp_spec_desc

save "${intermediate_data}/NRMP/specialty_applicant.dta", replace
